from flask import (
    Flask,
    render_template,
    g,
    request,
    abort,
    current_app,
    redirect,
    url_for,
    jsonify,
)
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select, case, func, true, false
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
)
from werkzeug.exceptions import HTTPException


app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///project.db"


class Base(DeclarativeBase):
    pass


db = SQLAlchemy(model_class=Base)
db.init_app(app)


class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    ipv4: Mapped[str] = mapped_column(unique=True)
    questions = relationship("UserQuestion", back_populates="user")

    def to_json(self):
        return {"id": self.id, "ipv4": self.ipv4}


class Question(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    q: Mapped[str] = mapped_column()
    users = relationship("UserQuestion", back_populates="question")


class UserQuestion(db.Model):
    user_id: Mapped[int] = mapped_column(db.ForeignKey("user.id"), primary_key=True)
    question_id: Mapped[int] = mapped_column(
        db.ForeignKey("question.id"), primary_key=True
    )
    answer: Mapped[bool] = mapped_column()
    user = relationship(User, back_populates="questions")
    question = relationship(Question, back_populates="users")


@app.cli.command("init-db")
def init_db():
    db.create_all()


@app.cli.command("clean-db")
def clean_db():
    db.drop_all()


@app.cli.command("fake-db")
def fake_db():
    from faker import Faker
    from faker.providers import internet

    fake = Faker("zh_CN")
    fake.add_provider(internet)

    users = [User(ipv4=fake.ipv4_private()) for _ in range(100)]
    questions = [Question(q=fake.text(120)) for _ in range(20)]
    db.session.add_all(users)
    db.session.add_all(questions)
    db.session.commit()


def get_real_ip():
    if request.headers.get("CF-Connecting-IP"):
        return request.headers.get("CF-Connecting-IP")
    elif request.headers.get("X-Forwarded-For"):
        return request.headers.get("X-Forwarded-For").split(",")[0].strip()
    else:
        return request.remote_addr


@app.before_request
def before_request():
    ipv4 = get_real_ip()
    sql = select(User).where(User.ipv4 == ipv4)
    u = db.session.execute(sql).scalars().first()
    if not u:
        db.session.add(User(ipv4=ipv4))
        db.session.commit()
    g.user = u


@app.errorhandler(HTTPException)
def http_error(e):
    return render_template("error.html", e=e)


@app.errorhandler(Exception)
def all_error(e):
    current_app.logger.exception("Stack Error: ")
    return render_template("error.html", e=e)


@app.route("/")
def index():
    yes_case = case((UserQuestion.answer == true(), 1), else_=0)
    no_case = case((UserQuestion.answer == false(), 1), else_=0)

    sql = (
        db.session.query(
            Question.id,
            Question.q,
            func.count(UserQuestion.answer).label("total_count"),
            func.sum(yes_case).label("yes_count"),
            func.sum(no_case).label("no_count"),
        )
        .outerjoin(UserQuestion, Question.id == UserQuestion.question_id)
        .group_by(Question.id, Question.q)
    )

    result = db.session.execute(sql).all()
    questions = []
    for row in result:
        yes_count = row.yes_count
        no_count = row.no_count
        total_count = row.total_count
        yes_percentage = (yes_count / total_count) * 100 if total_count > 0 else 0
        no_percentage = (no_count / total_count) * 100 if total_count > 0 else 0
        questions.append(
            {
                "id": row.id,
                "question": row.q,
                "yes_count": yes_count,
                "no_count": no_count,
                "yes_percentage": yes_percentage,
                "no_percentage": no_percentage,
            }
        )
    return render_template("index.html", questions=questions)


@app.route("/questions/<int:qid>", methods=["GET", "POST"])
def question_detail(qid):
    sql = select(Question).where(Question.id == qid)
    question = db.session.execute(sql).scalars().first()
    if not question:
        abort(404)

    if request.method == "POST":
        answer = request.form.get("answer")
        is_yes = answer == "Yes"

        user_question = (
            db.session.query(UserQuestion)
            .filter_by(user_id=g.user.id, question_id=qid)
            .first()
        )

        if user_question:
            user_question.answer = is_yes
        else:
            user_question = UserQuestion(
                user_id=g.user.id, question_id=qid, answer=is_yes
            )
            db.session.add(user_question)

        db.session.commit()
        return redirect(url_for("index"))

    return render_template("questions.html", question=question)


@app.route("/users")
def query_all_users():
    sql = select(User)
    users = db.session.execute(sql).scalars().all()
    return jsonify([u.to_json() for u in users])
